<?php
class SiteController extends CController
{
    public $layout = 'plain';
    /**
     * @var array context menu items. This property will be assigned to {@link CMenU::items}.
     */
    public $menu = array();
    /**
     * @var array the breadcrumbs of the current page. The value of this property will
     * be assigned to {@link CBreadcrumbs::links}. Please refer to {@link CBreadcrumbs::links}
     * for more details on how to specify this property.
     */
    public $breadcrumbs = array();
    public function filters()
    {
        return array(
            'accessControl',
        );
    }
    public function accessRules()
    {
        return array(
            array('allow',
                'users' => array('*'),
                'actions' => array('login'),
            ),
            array('allow',
                'users' => array('@'),
            ),
            array('deny',
                'users' => array('*'),
            ),
        );
    }
    public function actions()
    {
        return array(
            // captcha action renders the CAPTCHA image displayed on the contact page
            'captcha' => array(
                'class' => 'CCaptchaAction',
                'backColor' => 0xFFFFFF,
            ),
            // page action renders "static" pages stored under 'protected/views/site/pages'
            // They can be accessed via: index.php?r=site/page&view=FileName
            'page' => array('class' => 'CViewAction',),
        );
    }
    /**
     * This is the default 'index' action that is invoked
     * when an action is not explicitly requested by users.
     */
    public function actionIndex()
    {
        // renders the view file 'protected/views/site/index.php'
        // using the default layout 'protected/views/layouts/main.php'
        $this->layout = 'main';
        $this->render('index');
    }
    /**
     * This is the action to handle external exceptions.
     */
    public function actionError()
    {
        if ($error = Yii::app()->errorHandler->error) {
            if (Yii::app()->request->isAjaxRequest) echo $error['message'];
            else $this->render('error', $error);
        }
    }
    /**
     * Displays the contact page
     */
    public function actionContact()
    {
        $model = new ContactForm;
        if (isset($_POST['ContactForm'])) {
            $model->attributes = $_POST['ContactForm'];
            if ($model->validate()) {
                $name = '=?UTF-8?B?' . base64_encode($model->name) . '?=';
                $subject = '=?UTF-8?B?' . base64_encode($model->subject) . '?=';
                $headers = "From: $name <{$model->email}>\r\n" . "Reply-To: {$model->email}\r\n" .
                    "MIME-Version: 1.0\r\n" . "Content-type: text/plain; charset=UTF-8";
                mail(Yii::app()->params['adminEmail'], $subject, $model->body,
                    $headers);
                Yii::app()->user->setFlash('contact',
                    'Thank you for contacting us. We will respond to you as soon as possible.');
                $this->refresh();
            }
        }
        $this->render('contact', array('model' => $model));
    }
    /**
     * Displays the login page
     */
    //  public function actionLogin()
//    {
//        $model = new LoginForm;
//
//        // if it is ajax validation request
//        if (isset($_POST['ajax']) && $_POST['ajax'] === 'login-form')
//        {
//            echo CActiveForm::validate($model);
//            Yii::app()->end();
//        }
//
//        // collect user input data
//        if (isset($_POST['LoginForm']))
//        {
//            $model->attributes = $_POST['LoginForm'];
//            // validate user input and redirect to the previous page if valid
//            if ($model->validate() && $model->login())
//                $this->redirect(Yii::app()->user->returnUrl);
//        }
//        // display the login form
//        $this->render('login', array('model' => $model));
//    }
    /**
     * Logs out the current user and redirect to homepage.
     */
    public function actionGenerateGL()
    {
        return;
        Yii::app()->end();
        Yii::import('application.components.Gl');
        app()->db->autoCommit = false;
        $transaction = Yii::app()->db->beginTransaction();
        try {

            /* @var $pembelian Pembelian[] */
            $pembelian = Pembelian::model()->findAll("tgl >= :from and tipe = -1",
                array(":from" => '2014-09-01'));
            foreach ($pembelian as $beli) {
                $beli->disc = 0;
                $ppn = $persedian = $nominal = 0;
                $gl = new Gl();
                foreach ($beli->pembelianDetils as $beli_detil) {
                    if ($beli_detil->barang->ppn) {
                        $ppn += ($beli_detil->nominal) * (10 / 110);
                        $persedian += ($beli_detil->nominal) * (100 / 110);
                    } else {
                        $persedian += $beli_detil->nominal;
                    }
                    $nominal += $beli_detil->nominal;
                }
                $beli->sub_total = $nominal;
                $beli->total = $nominal;
                $beli->sisa_tagihan = $nominal;
                if (!$beli->save())
                    throw new Exception("Gagal menyimpan faktur pembelian. " . CHtml::errorSummary($beli));
                $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                    PERSEDIANBARANGDAGANG, '', '', $persedian);
                if ($ppn != 0) {
                    //ppn Debet
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        PPN, '', '', $ppn);
                }
                // biaya bongkar pembelian Debet
                if ($beli->biaya_bongkar != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        BONGKAR, '', '', $beli->biaya_bongkar);
                }
                // biaya angkut pembelian Debet
                if ($beli->biaya_angkut != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        ANGKUT, '', '', $beli->biaya_angkut);
                }
                // utang dagang Kredit
                if ($beli->sisa_tagihan != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        UTANGDAGANG, '', '', -$beli->sisa_tagihan);
                }
                // Kas Kredit
                if ($beli->uang_muka != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        $beli->idBank->account_code, '', '', -$beli->uang_muka);
                }
                // potongan pembelian Kredit
                if ($beli->disc != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        POTONGAN_PEMBELIAN, '', '', -$beli->disc);
                }
                // allowance dagang Kredit
                if ($beli->allowance != 0) {
                    $gl->add_gl(RETURPEMBELIAN, $beli->pembelian_id, $beli->tgl, $beli->no_faktur_beli,
                        ALLOWANCE, '', '', -$beli->allowance);
                }
                $gl->validate();
            }
            $transaction->commit();
            $status = true;
        } catch (Exception $ex) {
            $transaction->rollback();
            $status = false;
            $msg = $ex->getMessage();
        }
        echo CJSON::encode(array(
            'success' => $status,
            'msg' => $msg));
    }
    public function actionGenerateStockMovesDOS()
    {
        Yii::import('application.components.U');
        app()->db->autoCommit = false;
        $transaction = Yii::app()->db->beginTransaction();
        try {
            /* @var $dos Dos[] */
            $dos = Dos::model()->findAll("tgl >= :from",
                array(':from'=>'2014-09-01'));
            /* @var $dosdetils DosDetil[] */
//            $dosdetils = DosDetil::model()->findAll($criteria);
            foreach($dos as $dosh){
                foreach($dosh->dosDetils as $dosdetil){
                    U::add_stock_moves(DOS, $dosh->dos_id, $dosh->tgl,
                        $dosdetil->barang_id, GUDANG_PST, -$dosdetil->pcs, $dosh->doc_ref,
                        0);
                    U::add_stock_moves(DOS, $dosh->dos_id, $dosh->tgl,
                        $dosdetil->barang_id, GUDANG_SMT, $dosdetil->pcs, $dosh->doc_ref,
                        0);
                }
            }
            $transaction->commit();
            $status = true;
        } catch (Exception $ex) {
            $transaction->rollback();
            $status = false;
            $msg = $ex->getMessage();
        }
        echo CJSON::encode(array(
            'success' => $status,
            'msg' => $msg));
    }
    public function actionLogout()
    {
        Yii::app()->user->logout();
        $this->redirect(Yii::app()->homeUrl);
    }
    public function actionLogin()
    {
        Yii::import('application.components.UserIdentity');
        if (!Yii::app()->request->isAjaxRequest) {
            $this->layout = 'login';
            $this->render('login');
        } else {
            $model = new LoginForm;
            $loginUsername = isset($_POST["loginUsername"]) ? $_POST["loginUsername"]
                : "";
            $loginPassword = isset($_POST["loginPassword"]) ? $_POST["loginPassword"]
                : "";
            if ($loginUsername != "") {
                //$model->attributes = $_POST['LoginForm'];
                $model->username = $loginUsername;
                $model->password = $loginPassword;
                // validate user input and redirect to the previous page if valid
                if ($model->validate() && $model->login())
                    echo "{success: true}";
                else
                    echo "{success: false, errors: { reason: 'Login failed. Try again.' }}";
            } else {
                echo "{success: false, errors: { reason: 'Login failed. Try again' }}";
            }
        }
        //$loginUsername = "f";
        //if ($loginUsername == "f")
//        {
//            echo "{success: true}";
//        } else
//        {
//            echo "{success: false, errors: { reason: 'Login failed. Try again.' }}";
//        }
    }
    public function actionGenerate()
    {
        $templatePath = './css/silk_v013/icons';
        $files = scandir($templatePath);
        $txt = "";
        foreach ($files as $file) {
            if (is_file($templatePath . '/' . $file)) {
                $basename = explode(".", $file);
                $name = $basename[0];
                $txt .= ".silk13-$name { background-image: url(icons/$file) !important; background-repeat: no-repeat; }\n";
            }
        }
        $myFile = "silk013.css";
        $fh = fopen($myFile, 'w') or die("can't open file");
        fwrite($fh, $txt);
        fclose($fh);
    }
    public function actionTree()
    {
        Yii::import('application.components.MenuTree');
        $user = Users::model()->findByPk(user()->getId());
        $menu = new MenuTree($user->role_id);
        $data = $menu->get_menu();
//        $data = "[{text: 'Penjualan',
//                  expanded: false,
//                  children:[{
//                  text: 'Faktur Penjualan',
//                  id: 'jun.NotaGrid',
//                  leaf: true
//                  },
//                  {
//                  text: 'Sales dan Pelanggan',
//                  expanded: false,
//                  children:[{
//                    text: 'Managemen Pelanggan',
//                    id: 'jun.CustomersGrid',
//                    leaf: true
//                    }]
//                  }]
//                  },
//                  {text: 'Pembelian',
//                  expanded: false,
//                  children:[{
//                    text: 'Pembelian Kredit',
//                    id: 'jun.NotaGrid',
//                    leaf: true
//                    },
//                    {
//                    text: 'Pembelian Tunai',
//                    id: 'jun.NotaGrid',
//                    leaf: true
//                    },
//                    {
//                    text: 'Supplier',
//                    expanded: false,
//                    children:[{
//                        text: 'Managemen Supplier',
//                        id: 'jun.SuppliersGrid',
//                        leaf: true
//                        }]
//                    }]
//                  },
//                  {text: 'Item',
//                  expanded: false,
//                  children:[{
//                    text: 'Item',
//                    id: 'jun.StockMasterGrid',
//                    leaf: true
//                    },
//                    {
//                    text: 'Satuan Item',
//                    id: 'jun.ItemUnitsGrid',
//                    leaf: true
//                    },
//                    {
//                    text: 'Kategori Stok',
//                    id: 'jun.StockCategoryGrid',
//                    leaf: true
//                    }]
//                  },
//                  {text: 'Kas/Bank',
//                    expanded: false,
//                    children:[{
//                        text: 'Kas Keluar',
//                        id: 'jun.NotaGrid',
//                        leaf: true
//                        },{
//                        text: 'Kas Masuk',
//                        id: 'jun.NotaGrid',
//                        leaf: true
//                        },{
//                        text: 'Transfer Antar Bank',
//                        id: 'jun.TranferBankWin',
//                        leaf: true
//                        },{
//                        text: 'Penyesuain Bank',
//                        id: 'jun.NotaGrid',
//                        leaf: true
//                        },{
//                         text: 'Bank Statement',
//                            id: 'jun.BankTransGrid',
//                           leaf: true
//                        },
//                        {
//                        text: 'Managemen',
//                        expanded: false,
//                        children:[{
//                            text: 'Akun Bank',
//                            id: 'jun.BankAccountsGrid',
//                            leaf: true
//                            }]
//                        }]},
//                  {
//                    text:'Akuntansi',
//                    expanded: false,
//                    children:[{
//                    text: 'Input Jurnal',
//                    id: 'jun.TblUserGrid',
//                    leaf: true
//                    },{
//                    text: 'GL Inquiry',
//                    id: 'jun.GlTransGrid',
//                    leaf: true
//                    },{
//                        text: 'Managemen',
//                        expanded: false,
//                        children:[{
//                            text: 'Quick Entries',
//                            id: 'jun.NotaGrid',
//                            leaf: true
//                            },{
//                            text: 'Akun Rekening',
//                            id: 'jun.ChartMasterGrid',
//                            leaf: true
//                            },{
//                            text: 'Grup Akun Rekening',
//                            id: 'jun.ChartTypesGrid',
//                            leaf: true
//                            },{
//                            text: 'Kelas Akun Rekening',
//                            id: 'jun.ChartClassGrid',
//                            leaf: true
//                            }]
//                        }
//                    ]},
//                    {
//                                text: 'Setting',
//                                expanded: false,
//                                children:[{
//                                    text: 'Forms Setup',
//                                    id: 'jun.SysTypesGrid',
//                                    leaf: true
//                                },{
//                                    text: 'Profile',
//                                    id: 'jun.KlasfikasiDetailManfaatGrid',
//                                    leaf: true
//                                },{
//                                    text: 'Ubah Password',
//                                    id: 'jun.KlasfikasiDetailManfaatGrid',
//                                    leaf: true
//                                },{
//                                    text: 'Users',
//                                    id: 'jun.UsersGrid',
//                                    leaf: true
//                                }]
//
//                            }]";
        Yii::app()->end($data);
    }
    public function actionClosing()
    {
//        if (!Yii::app()->request->isAjaxRequest) return;
        if (isset($_POST) && !empty($_POST)) {
            $year = $_POST['year'];
            $tgl = $year . '-12-31';
            $transaction = Yii::app()->db->beginTransaction();
            echo "===== MULAI CLOSING TAHUN $year =====<br/>";
            try {
                echo date("d/m/y H:i:s"), ": ", "Ambil data transaksi jurnal<br/>";
                $gltrans = Yii::app()->db->createCommand("
                SELECT ngl.account_code,COALESCE(SUM(ngl.amount),0) amount
                FROM psn_gl_trans ngl WHERE ngl.tran_date <= :tgl
                GROUP BY ngl.account_code")
                    ->queryAll(true, array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Ambil data transaksi kas/bank<br/>";
                $banktrans = Yii::app()->db->createCommand("
                SELECT nbt.id_bank,COALESCE(SUM(nbt.amount),0) amount
                FROM psn_bank_trans nbt WHERE nbt.trans_date <= :tgl
                GROUP BY nbt.id_bank")
                    ->queryAll(true, array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Ambil data mutasi barang<br/>";
                $stockmove = Yii::app()->db->createCommand("
                SELECT nsm.barang_id,nsm.gudang_id,SUM(nsm.qty) qty
                FROM psn_stock_moves nsm WHERE nsm.tran_date <= :tgl
                GROUP BY nsm.barang_id,nsm.gudang_id")
                    ->queryAll(true, array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data setoran kas salesman<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_setor_kas_sales WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data retur botol<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_retur_botol WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data biaya salesman<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_biaya_sales WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data botol<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_botol WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data DO Sementara<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_dos WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data DO Final<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_dof WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data transaksi bank<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_bank_trans WHERE trans_date <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data transaksi jurnal<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_comments WHERE date_ <= :tgl;
                DELETE FROM psn_gl_trans WHERE tran_date <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data transaksi kas<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_kas WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data COGS<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_cogs WHERE tgl <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data mutasi barang<br/>";
                Yii::app()->db->createCommand("DELETE FROM psn_stock_moves WHERE tran_date <= :tgl;")
                    ->execute(array(':tgl' => $tgl));
                echo date("d/m/y H:i:s"), ": ", "Delete data penjualan<br/>";
                Yii::app()->db->createCommand("
                drop table if exists batch_to_delete;

                CREATE TEMPORARY TABLE batch_to_delete
                SELECT pp.penjualan_id FROM psn_penjualan pp
                    LEFT JOIN (SELECT pppd.penjualan_id,COALESCE(SUM(pppd.kas_diterima),0) kas_diterima FROM psn_pelunasan_piutang_detil pppd
                INNER JOIN psn_pelunasan_piutang as ppp ON pppd.pelunasan_piutang_id = ppp.pelunasan_piutang_id
                WHERE ppp.tgl <= :tgl
                GROUP BY pppd.penjualan_id) ppp ON pp.penjualan_id = ppp.penjualan_id
                WHERE pp.tgl <= :tgl  AND (pp.total - COALESCE(ppp.kas_diterima,0) - pp.uang_muka) = 0;
                create index batch_to_delete_ix_key on batch_to_delete(penjualan_id);

                delete pp from psn_penjualan pp
                join batch_to_delete d on d.penjualan_id = pp.penjualan_id;

                delete pp from psn_pelunasan_piutang_detil pp
                join batch_to_delete d on d.penjualan_id = pp.penjualan_id;

                DELETE ppp1 FROM psn_pelunasan_piutang ppp1 JOIN
                (SELECT ppp.pelunasan_piutang_id FROM psn_pelunasan_piutang ppp LEFT JOIN
                psn_pelunasan_piutang_detil pppd ON ppp.pelunasan_piutang_id = pppd.pelunasan_piutang_id
                WHERE ppp.tgl <= :tgl
                GROUP BY ppp.pelunasan_piutang_id
                HAVING COUNT(pppd.pelunasan_piutang_id) = 0) q
                ON q.pelunasan_piutang_id = ppp1.pelunasan_piutang_id;")->execute(array(':tgl' => $tgl));

                echo date("d/m/y H:i:s"), ": ", "Delete data pembelian<br/>";
                Yii::app()->db->createCommand("
                drop table if exists batch_to_delete;

                CREATE TEMPORARY TABLE batch_to_delete
                SELECT pp.pembelian_id FROM psn_pembelian AS pp
                LEFT  JOIN (select ppud.mode_id,COALESCE(SUM(ppud.kas_dibayar),0) kas_dibayar from psn_pelunasan_utang_detil AS ppud
                INNER JOIN psn_pelunasan_utang as ppu ON ppud.pelunasan_utang_id = ppu.pelunasan_utang_id
                WHERE ppud.trans_mode = 0 AND ppu.tgl <= :tgl
                GROUP BY ppud.mode_id) ppud ON pp.pembelian_id = ppud.mode_id
                WHERE pp.tipe = 1 AND pp.tgl <= :tgl AND
                (pp.sisa_tagihan - pp.uang_muka - COALESCE(ppud.kas_dibayar,0)) = 0;

                create index batch_to_delete_ix_key on batch_to_delete(pembelian_id);

                delete pp from psn_pembelian pp
                join batch_to_delete d on d.pembelian_id = pp.pembelian_id  AND pp.tipe = 1;

                delete pp from psn_pelunasan_utang_detil pp
                join batch_to_delete d on d.pembelian_id = pp.mode_id AND pp.trans_mode = 0;

                drop table if exists batch_to_delete;

                CREATE TEMPORARY TABLE batch_to_delete
                SELECT pp.pembelian_id FROM psn_pembelian AS pp
                LEFT  JOIN (select ppud.mode_id,COALESCE(SUM(ppud.kas_dibayar),0) kas_dibayar from psn_pelunasan_utang_detil AS ppud
                INNER JOIN psn_pelunasan_utang as ppu ON ppud.pelunasan_utang_id = ppu.pelunasan_utang_id
                WHERE ppud.trans_mode = 0 AND ppu.tgl <= :tgl
                GROUP BY ppud.mode_id) ppud ON pp.pembelian_id = ppud.mode_id
                WHERE pp.tipe = -1 AND pp.tgl <= :tgl AND
                (pp.sisa_tagihan - pp.uang_muka - COALESCE(ppud.kas_dibayar,0)) = 0;

                create index batch_to_delete_ix_key on batch_to_delete(pembelian_id);

                delete pp from psn_pembelian pp
                join batch_to_delete d on d.pembelian_id = pp.pembelian_id  AND pp.tipe = -1;

                delete pp from psn_pelunasan_utang_detil pp
                join batch_to_delete d on d.pembelian_id = pp.mode_id AND pp.trans_mode = 0;

                drop table if exists batch_to_delete;

                CREATE TEMPORARY TABLE batch_to_delete
                SELECT pnd.nota_debet_id FROM psn_nota_debet AS pnd
                LEFT JOIN (select ppud.mode_id,COALESCE(SUM(ppud.kas_dibayar),0) kas_dibayar from psn_pelunasan_utang_detil AS ppud
                INNER JOIN psn_pelunasan_utang as ppu ON ppud.pelunasan_utang_id = ppu.pelunasan_utang_id
                WHERE ppud.trans_mode = 1 AND ppu.tgl <= :tgl
                GROUP BY ppud.mode_id) ppud ON pnd.nota_debet_id = ppud.mode_id
                WHERE pnd.tgl <= :tgl AND pnd.total - COALESCE(ppud.kas_dibayar,0) = 0;

                create index batch_to_delete_ix_key on batch_to_delete(nota_debet_id);

                delete pp from psn_nota_debet pp
                join batch_to_delete d on d.nota_debet_id = pp.nota_debet_id;

                delete pp from psn_pelunasan_utang_detil pp
                join batch_to_delete d on d.nota_debet_id = pp.mode_id AND pp.trans_mode = 1;

                DELETE ppu1 FROM psn_pelunasan_utang ppu1 JOIN
                (SELECT ppu.pelunasan_utang_id FROM psn_pelunasan_utang ppu LEFT JOIN
                psn_pelunasan_utang_detil ppud ON ppu.pelunasan_utang_id = ppud.pelunasan_utang_id
                WHERE ppu.tgl  <= :tgl
                GROUP BY ppu.pelunasan_utang_id
                HAVING COUNT(ppud.pelunasan_utang_id) = 0) q
                ON q.pelunasan_utang_id = ppu1.pelunasan_utang_id;")
                    ->execute(array(':tgl' => $tgl));

                echo date("d/m/y H:i:s"), ": ", "Input data saldo awal jurnal<br/>";
                foreach ($gltrans as $row) {
                    if ($row['amount'] == 0 || preg_match('/^(4|5|6)\w.*/', $row['account_code'])) {
                        continue;
                    }
                    $gl = new GlTrans;
                    $gl->type = SALDO_AWAL;
                    $gl->type_no = $year;
                    $gl->tran_date = $tgl;
                    $gl->memo_ = "Closing $year";
                    $gl->amount = $row['amount'];
                    $gl->id_user = Yii::app()->user->getId();
                    if($row['account_code'] == COA_LABA_RUGI){
                        $row['account_code'] = COA_LABA_DITAHAN;
                    }
                    $gl->account_code = $row['account_code'];
                    $gl->tdate = new CDbExpression('NOW()');
                    if (!$gl->save())
                        throw new Exception(t('save.model.fail', 'app', array('{model}' => 'General Journal')) . CHtml::errorSummary($gl));
                    echo date("d/m/y H:i:s"), ": ", "--- ", $gl->account_code, " ", number_format($gl->amount,2),"<br/>";
                }
                echo date("d/m/y H:i:s"), ": ", "Input data saldo awal bank<br/>";
                foreach ($banktrans as $row) {
                    if ($row['amount'] == 0) {
                        continue;
                    }
                    $bt = new BankTrans;
                    $bt->ref = "-";
                    $bt->type = SALDO_AWAL;
                    $bt->trans_no = $year;
                    $bt->trans_date = $tgl;
                    $bt->amount = $row['amount'];
                    $bt->id_user = Yii::app()->user->getId();
                    $bt->tdate = new CDbExpression('NOW()');
                    $bt->id_bank = $row['id_bank'];
                    if (!$bt->save())
                        throw new Exception(t('save.model.fail', 'app', array('{model}' => 'Bank Trans')) . CHtml::errorSummary($bt));
                    echo date("d/m/y H:i:s"), ": ", "--- ", $bt->idBank->nama_bank, " ", number_format($bt->amount,2),"<br/>";
                }
                echo date("d/m/y H:i:s"), ": ", "Input data saldo awal barang<br/>";
                foreach ($stockmove as $row) {
                    if ($row['qty'] == 0) {
                        continue;
                    }
                    /* @var $barang Barang */
                    $barang = Barang::model()->findByPk($row['barang_id']);
//                    $barang = new Barang();
                    $stm = new StockMoves;
                    $stm->reference = "-";
                    $stm->type = SALDO_AWAL;
                    $stm->trans_no = $year;
                    $stm->tran_date = $tgl;
                    $stm->price = $barang->price_buy;
                    $stm->qty = $row['qty'];
                    $stm->barang_id = $row['barang_id'];
                    $stm->gudang_id = $row['gudang_id'];
                    if (!$stm->save())
                        throw new Exception(t('save.model.fail', 'app', array('{model}' => 'Bank Trans')) . CHtml::errorSummary($stm));
                    echo date("d/m/y H:i:s"), ": ", "--- ", $stm->barang->barang_name, " ",$stm->gudang->gudang_name,
                    " ",number_format($stm->qty),"<br/>";
                }
                $closing = new Closing();
                $closing->year = $year;
                $closing->tdate = new CDbExpression('NOW()');
                if (!$closing->save())
                    throw new Exception(t('save.model.fail', 'app',
                            array('{model}' => 'Closing')) . CHtml::errorSummary($closing));
                $transaction->rollback();
                echo date("d/m/y H:i:s"), ": ", "Closing successfuly<br/>";
//                $msg = 'Closing successfuly...';
                $status = true;
                echo "===== CLOSING TAHUN $year SELESAI =====<br/>";
            } catch (Exception $ex) {
                $transaction->rollback();
//                $msg = $ex->getMessage();
                echo date("d/m/y H:i:s"), ": ", $ex->getMessage(),"<br/>";
                $status = false;
            }
            Yii::app()->end();
        }
    }
    public function actionBackupAll()
    {
        if (!Yii::app()->request->isPostRequest) {
            $this->redirect(bu());
        }
        $conn = Yii::app()->db;
        $user = $conn->username;
        $pass = $conn->password;
        if (preg_match('/^mysql:host=(.*);dbname=(.*);(?:port=(.*))?/', $conn->connectionString, $result)) {
            list($all, $host, $db, $port) = $result;
        }
        $dir_backup = dirname(Yii::app()->request->scriptFile) . "\\backup\\";
        $files = scandir($dir_backup);
        foreach ($files as $file) {
            if (is_file("$dir_backup\\$file")) {
                unlink("$dir_backup\\$file");
            }
        }
        $backup_file = $dir_backup . $db . date("Y-m-d-H-i-s") . '.pos';
        $mysqldump = dirname(Yii::app()->request->scriptFile) . "\\mysqldump";
        $gzip = dirname(Yii::app()->request->scriptFile) . "\\gzip";
        $command = "$mysqldump --opt -h $host -u $user --password=$pass -P $port " .
            "$db > $backup_file";
        system($command);
        $size = filesize($backup_file);
        if ($size > 0) {
            $command = "$gzip -9 $backup_file";
            system($command);
            $size = filesize("$backup_file.gz");
            $bu = bu() . "/backup/" . basename("$backup_file.gz");
            header("Location: $bu");
        }
    }
    public function actionRestoreAll()
    {
        if (!Yii::app()->request->isPostRequest) {
            $this->redirect(bu());
        }
        $conn = Yii::app()->db;
        $user = $conn->username;
        $pass = $conn->password;
        if (preg_match('/^mysql:host=(.*);dbname=(.*);(?:port=(.*))?/', $conn->connectionString, $result)) {
            list($all, $host, $db, $port) = $result;
        }
        $dir_backup = dirname(Yii::app()->request->scriptFile) . "\\backup\\";
        if (isset($_FILES["filename"])) { // it is recommended to check file type and size here
            if ($_FILES["filename"]["error"] > 0) {
                echo CJSON::encode(array(
                    'success' => false,
                    'msg' => $_FILES["file"]["error"]
                ));
            } else {
                $backup_file = $dir_backup . $_FILES["filename"]["name"];
                move_uploaded_file($_FILES["filename"]["tmp_name"], $backup_file);
                $mysql = dirname(Yii::app()->request->scriptFile) . "\\mysql";
                $gzip = dirname(Yii::app()->request->scriptFile) . "\\gzip";
                $command = "$gzip -d $backup_file";
                system($command);
                $backup_file = substr($backup_file, 0, -3);
                if (!file_exists($backup_file)) {
                    echo CJSON::encode(array(
                        'success' => false,
                        'msg' => "Failed restore file " . $_FILES["file"]["name"]
                    ));
                } else {
                    Yii::app()->db->createCommand("DROP DATABASE $db")
                        ->execute();
                    Yii::app()->db->createCommand("CREATE DATABASE IF NOT EXISTS $db")
                        ->execute();
                    $command = "$mysql -h $host -u $user --password=$pass -P $port " .
                        "$db < $backup_file";
                    system($command);
                    echo CJSON::encode(array(
                        'success' => true,
                        'msg' => "Succefully restore file " . $_FILES["file"]["name"]
                    ));
                }
                Yii::app()->end();
            }
        }
    }
}
